package lesfiersarepasser.assoc.bdd;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import lesfiersarepasser.assoc.bean.*;

public class DerbyConnection {

	static String connectionURL = "jdbc:mysql://localhost/db_assoc";
	static String user = "db_assoc_user";
	static String pass = "db_assoc_pass";
	static Connection conn = null;

	public static void dbConnect() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(connectionURL, user, pass);
		} catch (SQLException | ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public static void dbDisconnect() {
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static String connectUser(String login, String password) {
		dbConnect();
		String vreturn = null;
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st
					.executeQuery("select * from UTILISATEUR where LOGIN = '"
							+ login + "'");

			while (rs.next()) {
				if (password.equals(rs.getString("PASSWORD"))) {
					vreturn = rs.getString("LOGIN");
				}
			}
		} catch (SQLException e) {
			vreturn = null;
			e.printStackTrace();
		}
		return vreturn;

	}

	public static boolean registerUser(Utilisateur user) {
		boolean vreturn = false;
		dbConnect();
		try {
			Statement st = conn.createStatement();
			vreturn = st.execute("insert into UTILISATEUR values("
					+ "(select max(ID) from UTILISATEUR)+1, '"
					+ user.getIdentifiant() + "', '" + user.getMdp() + "', '"
					+ user.getNom() + "', '" + user.getPrenom() + "', '"
					+ user.getAdresse() + "', '" + user.getCodePostale()
					+ "', '" + user.getVille() + "'");
		} catch (SQLException e) {
			vreturn = false;
			e.printStackTrace();
		}

		return vreturn;

	}

	public static ArrayList<Articles> getArticles() {
		ArrayList<Articles> vreturn = new ArrayList<Articles>();
		dbConnect();

		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st.executeQuery("SELECT * from ARTICLE");
			while (rs.next()) {
				vreturn.add(new Articles(rs.getString("CODE"), rs
						.getString("NOM"), rs.getDouble("PRIX")));
			}
		} catch (SQLException e) {
			vreturn = null;
			e.printStackTrace();
		}
		return vreturn;
	}

	public static ArrayList<Commande> getUserOrders(String login) {
		ArrayList<Commande> vreturn = new ArrayList<Commande>();
		dbConnect();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT c.ID, IDUTILISATEUR, FLAG from COMMANDE c "
							+ "INNER JOIN UTILISATEUR u on u.ID = c.IDUTILISATEUR "
							+ "where u.LOGIN = '" + login + "'");
			while (rs.next()) {
				Commande c = new Commande(rs.getInt(1), rs.getInt(2),
						getOrderArticles(rs.getInt(1)), rs.getInt(3));
				vreturn.add(c);
			}
		} catch (SQLException e) {
			vreturn = null;
			e.printStackTrace();
		}
		
		return vreturn;

	}

	public static HashMap<Articles, Integer> getOrderArticles(int order) {
		HashMap<Articles, Integer> vreturn = new HashMap<Articles, Integer>();
		dbConnect();
		Statement st;
		try {
			st = conn.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT a.CODE, a.NOM, a.PRIX, lc.QUANTITE "
							+ "from LISTECOMMANDE lc "
							+ "INNER JOIN ARTICLE a on a.CODE = lc.CODEARTICLE "
							+ "where lc.IDCOMMANDE = " + order);
			while (rs.next()) {
				Articles a = new Articles(rs.getString("CODE"),
						rs.getString("NOM"), rs.getDouble("PRIX"));
				vreturn.put(a, rs.getInt("QUANTITE"));
			}
		} catch (SQLException e) {
			vreturn = null;
			e.printStackTrace();
		}

		return vreturn;
	}

	public static boolean addArticleOrder(String login, String ref, int quantite) {
		boolean vreturn = false;
		dbConnect();
		try {
			Statement st = conn.createStatement();
			ResultSet rs = st
					.executeQuery("SELECT count(*) as existe from COMMANDE c "
							+ "INNER JOIN UTILISATEUR u on c.IDUTILISATEUR = u.ID "
							+ "where u.LOGIN = '" + login + "' and c.FLAG=0");

			rs.first();

			if (rs.getInt("existe") == 0) {
				createOrder(login);
			}

			rs = st.executeQuery("SELECT count(*) as existe from LISTECOMMANDE lc "
					+ "INNER JOIN COMMANDE c on lc.IDCOMMANDE = c.ID "
					+ "INNER JOIN UTILISATEUR u on c.IDUTILISATEUR = u.ID "
					+ "where u.LOGIN = '"
					+ login
					+ "' and lc.CODEARTICLE='"
					+ ref + "'");

			rs.first();

			if (rs.getInt("existe") != 0) {
				vreturn = st.execute("UPDATE LISTECOMMANDE as lc "
						+ "inner join COMMANDE c on c.ID = lc.IDCOMMANDE "
						+ "inner join UTILISATEUR u on c.IDUTILISATEUR = u.ID "
						+ "SET QUANTITE = quantite +" + quantite
						+ " where u.LOGIN = '" + login
						+ "' and lc.CODEARTICLE = '" + ref + "'");
			} else {
				vreturn = st
						.execute("insert into LISTECOMMANDE values("
								+ "(select c.id from COMMANDE c inner join UTILISATEUR u on c.IDUTILISATEUR = u.ID "
								+ "where u.LOGIN = '" + login
								+ "' and c.FLAG = 0), '" + ref + "', "
								+ quantite + ")");
			}

		} catch (SQLException e) {
			vreturn = false;
			e.printStackTrace();
		}

		return vreturn;
	}

	public static boolean createOrder(String login) {
		boolean vreturn = false;
		dbConnect();
		try {
			Statement st = conn.createStatement();
			vreturn = st
					.execute("insert into COMMANDE values(default, (SELECT u.ID from UTILISATEUR u where u.LOGIN = '"
							+ login + "')," + "0)");
		} catch (SQLException e) {
			vreturn = false;
			e.printStackTrace();
		}

		return vreturn;
	}
	
	public static boolean validateOrder(String id) {
		boolean vreturn = false;
		dbConnect();
		try {
			Statement st = conn.createStatement();
			System.out.println("update COMMANDE set flag=1 where ID="+id);
			vreturn = st
					.execute("update COMMANDE set flag=1 where ID="+id);
		} catch (SQLException e) {
			vreturn = false;
			e.printStackTrace();
		}

		return vreturn;
	}
	
	

}
